﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace Battleship
{
    public class iExcelReader
    {
        public Microsoft.Office.Interop.Excel.Application appExcel;
        public Workbook newWorkbook = null;
        public _Worksheet objsheet = null;

        public iExcelReader(string path)
        {
            excel_init(path);
        }
        //Method to initialize opening Excel
        public void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                Worksheet sheet = (Worksheet)newWorkbook.Sheets[1];
                //excel_showValues(sheet);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                Console.WriteLine("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
            }
        }
        public PortList excel_readToPort()
        {

            Worksheet sheet = (Worksheet)newWorkbook.Sheets[1];
            Worksheet sheet2 = (Worksheet)newWorkbook.Sheets[2];

            Range exRange = sheet.UsedRange;
            Range exRange2 = sheet2.UsedRange;

            int num_row = exRange.Rows.Count;
            int num_row2 = exRange2.Rows.Count;

            int num_col = exRange.Columns.Count;
            int num_col2 = exRange2.Columns.Count;

            object[] data = new object[num_col];

            PortList portList = new PortList();

            for (int i = 2; i <= num_row2; i++)
            {


                Microsoft.Office.Interop.Excel.Range objRange = (Microsoft.Office.Interop.Excel.Range)sheet2.Cells[i, 1];
                string strPN = objRange.get_Value().ToString();
                objRange = (Microsoft.Office.Interop.Excel.Range)sheet2.Cells[i, 2];
                string strPI = objRange.get_Value().ToString();


                Port tmp = new Port(strPN, Int16.Parse(strPI));
                portList.content.Add(tmp);
            }

            return portList;
        }
        public List<ContainerInfo> excel_readToContainer(PortList portList)
        {

            Worksheet sheet = (Worksheet)newWorkbook.Sheets[1];
            Worksheet sheet2 = (Worksheet)newWorkbook.Sheets[2];

            Range exRange = sheet.UsedRange;
            Range exRange2 = sheet2.UsedRange;

            int num_row = exRange.Rows.Count;
            int num_row2 = exRange2.Rows.Count;

            int num_col = exRange.Columns.Count;
            int num_col2 = exRange2.Columns.Count;

            object[] data = new object[num_col];

            List<string> dataList = new List<string>();
            List<ContainerInfo> containerList = new List<ContainerInfo>();


            // Container
            int no = 0;
            for (int i = 2; i <= num_row; i++)
            {
                for (int j = 1; j <= num_col; j++)
                {

                    Microsoft.Office.Interop.Excel.Range objRange = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, j];
                    string strData = objRange.get_Value().ToString();
                    //Console.Write(strData);
                    // Read info into container
                    dataList.Add(strData);
                }
                ContainerInfo tmp = new ContainerInfo(++no, dataList, portList);
                dataList.Clear();
                containerList.Add(tmp);
            }


            return containerList;
        }
        //Method to get value; cellname is A1,A2, or B1,B2 etc...in excel.
        public string excel_getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }

            return value;
        }
        public void excel_close()
        {
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;
                    Console.WriteLine("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }
    }
}
